﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration.Install;
using System.Data.SqlClient;
using System.IO;

using System.Reflection;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System.Data;
using System.Text.RegularExpressions;

namespace SqlInstall
{
    [RunInstaller(true)]
    public partial class CustomInstaller : Installer
    {
        private string logFilePath = "C:\\SetupLog.txt";
        public CustomInstaller()
        {
            //This call is required by the Component Designer.
            //Add initialization code after the call to InitializeComponent
            InitializeComponent();
        }

        private string GetSql(string Name)
        {
            try
            {
                Log("\n (32) GetSQL---->Name:" + Name);
                // Gets the current assembly.
                Assembly Asm = Assembly.GetExecutingAssembly();
                Log("\n (35)            ---->Asm:"+Asm.GetName().Name + "+" + Name);
                // Resources are named using a fully qualified name.
                Stream strm = Asm.GetManifestResourceStream(Asm.GetName().Name + "." + Name);
                //Log("\n (38)            ---->Stream:"+ strm.ToString());
                // Reads the contents of the embedded file.
                StreamReader reader = new StreamReader(strm);
                Log("\n (41)            ---->Reader");
                return reader.ReadToEnd();
            }
            catch (Exception ex)
            {
                Log("\n (45) Ex:" + ex.ToString());
                Log("\n (46) Stack:" + ex.StackTrace.ToString());
                throw ex;
            }
        }
        private void ExecuteSql(string serverName, string dbName, string Sql)
        {
            Log("\n (52) ExecuteSql--->serverName:" + serverName + ";dbName:" + dbName + ";Sql:" + Sql + "\n");
            string connStr = string.Empty;
            string username = this.Context.Parameters["username"];
            string password = this.Context.Parameters["password"];
            if(password!=string.Empty && username != string.Empty)
                connStr= @"Data Source=" + serverName + ";User ID="+username + ";Password="+ password+";Initial Catalog=" + dbName + ";Integrated Security=True";
            else
                connStr = @"Data Source=" + serverName + ";User ID=sa;Password=1;Initial Catalog=" + dbName + ";Integrated Security=True";

            Log("\n (54) ExecuteSql--->ConnectionStr:" + connStr);
            if (connStr.Contains("\\"))
                connStr = connStr.Remove(connStr.IndexOf("\\"), 1);            
            Log("\n (57) ExecuteSql--->ConnectionStr:" + connStr);
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                try
                {
                    //Before Migration
                    //Server server = new Server(new ServerConnection(conn));
                    //server.ConnectionContext.ExecuteNonQuery(Sql);
                    //After Migration
                    string[] splitter = new string[] { "\r\nGO\r\n" };
                    string[] commandTexts = Sql.Split(splitter, StringSplitOptions.RemoveEmptyEntries);                    
                    //Regex regex = new Regex("^GO",RegexOptions.IgnoreCase | RegexOptions.Multiline);
                    //string[] commandTexts = regex.Split(Sql);

                    SqlCommand cmd;
                    foreach (string commandText in commandTexts)
                    {
                        if (conn.State == ConnectionState.Closed)                        
                            conn.Open();                        
                        string x = commandText.Replace("GO", "");
                        Log("\n (73) ExecuteSql---> ConnectionStr:" + conn.ConnectionString);
                        cmd = new SqlCommand(x, conn);
                        {
                            cmd.ExecuteNonQuery();
                        }
                        Log("\n (78) ExecuteSql--->Command:" + cmd.CommandText);
                    }
                }
                catch (Exception ex)
                {
                    Log("\n (83) Ex:" + ex.ToString());
                    Log("\n (84) Stack:" + ex.StackTrace.ToString());
                }
            }
        }
        protected void AddDBTable(string serverName)
        {
            try
            {
                //serverName = @"(LOCAL)\SQL2005";
                
                // Creates the database and installs the tables.          
                string strScript = GetSql("Clinic.txt");
                Log("\n (94): AddDBTable--->ServerName:"+serverName);
                ExecuteSql(serverName, "master", strScript);
                Log("\n (96): AddDBTable--->ServerName:" + serverName);
            }
            catch (Exception ex)
            {
                //Reports any errors and abort.
                Log("\n (101) Ex:" + ex.ToString());
                Log("\n (102) Stack:" + ex.StackTrace.ToString());
                throw ex;
            }
        }

        public override void Install(System.Collections.IDictionary stateSaver)
        {
            try
            {
                base.Install(stateSaver);
                Log("\n (112) Install--->Setup started++++++++++++++++++++");
                Log("ServerName:" + this.Context.Parameters["servername"]);
                AddDBTable(this.Context.Parameters["servername"]);
            }
            catch (Exception ex)
            {
                //Reports any errors and abort.
                Log("\n (119) Ex:" + ex.ToString());
                Log("\n (120) Stack:" + ex.StackTrace.ToString());
                throw ex;
            }
        }
        public void Log(string str)
        {
            StreamWriter Tex;
            try
            {
                Tex = File.AppendText(this.logFilePath);
                Tex.WriteLine(DateTime.Now.ToString() + " " + str);
                Tex.Close();
            }
            catch
            { }
        }

    }
}

